package com.casework

import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Workbook
import org.joda.time.LocalDate

class ExcelService {


    private List headings = [
            "Date",
            "User",
            "User Company",
            "Search Term",
            "Notifying Parties",
            "Filter by Industry – NACE Description",
            "Filter by Competition Concern - Horizontal",
            "Filter by Competition Concern – Vertical",
            "Filter by Competition Concern - Conglomerate",
            "Filter by Competition Concern – Mixed",
            "Filter by Cases – EC Number",
            "Filter by Cases – Year of Decision From",
            "Filter by Cases – Year of Decision To",
            "Filter by Cases – Phase",
            "Filter by Cases – Relative to SO",
            "Filter by Measure – Cluster Sale",
            "Filter by Measure – Alternative Remedy",
            "Filter by Measure – Purchaser Requirements",
            "Filter by Measure Classification - Exit from Joint Venture",
            "Filter by Measure Classification - Divestiture of a business unit carved out from a company structure",
            "Filter by Measure Classification - Divestiture of a controlling stake in a viable standalone business",
            "Filter by Measure Classification - Divestiture of assets package",
            "Filter by Measure Classification - Divestiture or grant of long-term licence",
            "Filter by Measure Classification - Granting of access to infrastructure",
            "Filter by Measure Classification - Granting of access to technology",
            "Filter by Measure Classification - Other behavioural",
            "Filter by Measure Classification - Other behavioural - governance provision",
            "Filter by Measure Classification - Other behavioural - IP / regulatory related",
            "Filter by Measure Classification - Other behavioural - selling or supply agreements",
            "Filter by Measure Classification - Other Structural Commitments",
            "Filter by Measure Classification - Termination of exclusive vertical agreements",
            "Results by - Case",
            "Results by - Measure"]

    File generateExcelSpreadSheet(LocalDate from, LocalDate to) {
        // create a new file
//        FileOutputStream out = new FileOutputStream("workbook.xls")
        Workbook workbook = new HSSFWorkbook()
        Sheet sheet = workbook.createSheet()

        workbook.setSheetName(0, "Search Results")

        int rowCount = 0
        Row row = sheet.createRow(rowCount++)
            headings.eachWithIndex {heading, index ->
                Cell cell = row.createCell(index)
                cell.setCellValue(heading)
            }

        int count = SearchQuery.countByDateOfSearchGreaterThanAndDateOfSearchLessThanEquals(from, to )

        def paginationNumber = 0
        while (count > paginationNumber) {
            def querys = SearchQuery.findAllByDateOfSearchGreaterThanAndDateOfSearchLessThanEquals(from, to, [max:100, sort:"dateOfSearch", order:"desc", offset:paginationNumber])

            paginationNumber += 100

            querys.eachWithIndex {SearchQuery query, index ->
                int cellCount = 0
                row = sheet.createRow(rowCount++)
                def cell = row.createCell(cellCount++)
                cell.setCellValue(query.dateOfSearch.toString())

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.user?.username ?: '')

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.user?.company?.name ?: '')

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.searchTerm ?: '')

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.firms ?: '')

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.shortNace)

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.competitionConcern.contains(CompetitionConcern.Horizontal.toString()))

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.competitionConcern.contains(CompetitionConcern.Vertical.toString()))

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.competitionConcern.contains(CompetitionConcern.Conglomerate.toString()))

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.competitionConcern.contains(CompetitionConcern.Mixed.toString()))

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.ecNumber)

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.fromDecisionYear?.toString() ?: '')

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.toDecisionYear?.toString() ?: '')

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.phase.join(', '))

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.relativeToSO.join(', '))

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.clusterSale.join(', '))

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.alternativeRemedy.join(', '))

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.purchaserCriteria.join(', '))

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.classification ? query.classification.contains('Exit from Joint Venture') : false)

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.classification ? query.classification.contains("Divestiture of a business unit carved out from a company structure") : false)

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.classification ? query.classification.contains("Divestiture of a controlling stake in a viable standalone business") : false)

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.classification ? query.classification.contains("Divestiture of assets package") : false)

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.classification ? query.classification.contains("Divestiture or grant of long-term licence") : false)

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.classification ? query.classification.contains("Granting of access to infrastructure") : false)

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.classification ? query.classification.contains("Granting of access to technology") : false)

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.classification ? query.classification.contains("Other behavioural") : false)

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.classification ? query.classification.contains("Other behavioural - governance provision") : false)

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.classification ? query.classification.contains("Other behavioural - IP / regulatory related") : false)

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.classification ? query.classification.contains("Other behavioural - selling or supply agreements") : false)

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.classification ? query.classification.contains("Other Structural Commitments") : false)

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.classification ? query.classification.contains("Termination of exclusive vertical agreements") : false)

                cell = row.createCell(cellCount++)
                cell.setCellValue(query.searchCases)

                cell = row.createCell(cellCount++)
                cell.setCellValue(!query.searchCases)

            }
        }
        // write the workbook to the output stream
        // close our file (don't blow out our file handles

        File file = new File('tmp')
        FileOutputStream outputStream = new FileOutputStream(file)
        workbook.write(outputStream)
        return file
    }
}
